﻿using System.Threading.Tasks;
using System;
using Microsoft.EntityFrameworkCore;
using Oracle.ManagedDataAccess.Client;
using Microsoft.AspNetCore.Http;
using Simple.Services.System;
using System.Data;

namespace Simple.Services;

public class QHSEXySpecialtyViolationService
{
    private readonly SimpleDbContext _context;
    private readonly CacheService _cacheService;
    public QHSEXySpecialtyViolationService(SimpleDbContext context, CacheService cacheService)
    {
        _context = context;
        _cacheService = cacheService;
    }
    public async Task<List<QHSEXyViolationClauseModel>> GetAsync()
    {
        string name = "11";
        var models = await _cacheService.GetQHSEViolationClauseAsync(name);
        if (models.Count == 0)
        {
            var query =_context.Set<QHSESysXyViolationClause>().AsNoTracking().AsQueryable();
            var big = await query.Where(u => u.SpecialtyCode == null).ToListAsync();
            var specialtyViolations =await query.Join(_context.Set<QHSESysKeyWorkSpecialty>(),
                clause => clause.SpecialtyCode,
                specialty => specialty.SpecialtyCode,
                (clause, specialty) => new { Clause = clause, Specialty = specialty })
                .Select(join => new QHSESysXyViolationClause
                    {
                        GeneralSpecialtyId=join.Clause.GeneralSpecialtyId,
                        SpecialtyCode= join.Clause.SpecialtyCode,
                        ViolationTypeCode =join.Clause.ViolationTypeCode,
                        Description = "("+join.Specialty.Specialty+")"+join.Clause.No+join.Clause.Description,
                        Note = join.Clause.Note,
                        No = join.Clause.No
                    }
                ).ToListAsync();
            specialtyViolations.AddRange(big);
            models = MapperHelper.Map<List<QHSEXyViolationClauseModel>>(specialtyViolations);
            await _cacheService.SetQHSEViolationClauseAsync(models, name);
        }
        
        return models;
    }
    public async Task<string> GetIdByItemAsync(string item)
    {
        var query = _context.Set<QHSESysXyViolationClause>().AsNoTracking().AsQueryable();
        var id = await query.Where(u => u.SpecialtyCode == null && item.Equals(u.Description)).Select(u=>u.GeneralSpecialtyId).FirstOrDefaultAsync();
        if(id == null)
        {
            id = await query.Join(_context.Set<QHSESysKeyWorkSpecialty>(),
            clause => clause.SpecialtyCode,
            specialty => specialty.SpecialtyCode,
            (clause, specialty) => new { Clause = clause, Specialty = specialty })
        .Where(u=>item.Equals("(" + u.Specialty.Specialty + ")" + u.Clause.No + u.Clause.Description))
            .Select(u=>u.Clause.GeneralSpecialtyId).FirstOrDefaultAsync();
        }
        return id;
    }
    public async Task<List<QHSEXyViolationClauseModel>> GetByNameAsync(IdInputModel model)
    {
        var models = await _cacheService.GetQHSEViolationClauseAsync(model.Id);
        if (models.Count == 0)
        {
            var query = _context.Set<QHSESysXyViolationClause>().AsNoTracking().AsQueryable();
            var specialtyViolations = await query.Join(_context.Set<QHSESysKeyWorkSpecialty>(),
                clause => clause.SpecialtyCode,
                specialty => specialty.SpecialtyCode,
                (clause, specialty) => new { Clause = clause, Specialty = specialty })
                
                .Select(join => new QHSESysXyViolationClause
                {
                    GeneralSpecialtyId = join.Clause.GeneralSpecialtyId,
                    SpecialtyCode = join.Clause.SpecialtyCode,
                    ViolationTypeCode = join.Clause.ViolationTypeCode,
                    Description = "(" + join.Specialty.Specialty + ")" + join.Clause.No + join.Clause.Description,
                    Note = join.Clause.Note,
                    No = join.Clause.No
                }
                ).Where(u=> EF.Functions.Like(u.Description, $"%{model.Id}%")).ToListAsync();
            models = MapperHelper.Map<List<QHSEXyViolationClauseModel>>(specialtyViolations);
            await _cacheService.SetQHSEViolationClauseAsync(models,model.Id);
        }

        return models;
    }
    public async Task<PageResultModel<QHSEXyViolationClauseModel>> QueryByCodesAsync(SpecialtyViolationInputModel input)
    {
        var result = new PageResultModel<QHSEXyViolationClauseModel>();
        var query = _context.Set<QHSESysXyViolationClause>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.SpecialtyCode))
        {
            query = query.Where(u => EF.Functions.Like(u.SpecialtyCode, $"%{input.SpecialtyCode}%"));

        }
        if (!string.IsNullOrEmpty(input.ViolationTypeCode))
        {
            query = query.Where(u => EF.Functions.Like(u.ViolationTypeCode, $"%{input.ViolationTypeCode}%"));

        }
        // 获取总数量
        result.TotalRows = await query.CountAsync();

        // 分页查询
        input.PageSize = result.TotalRows;
        query = query.Page(input.PageNo, input.PageSize);
        var specialtyViolations = await query.ToListAsync();
        result.Rows = MapperHelper.Map<List<QHSEXyViolationClauseModel>>(specialtyViolations);

        result.SetPage(input);
        result.CountTotalPage();

        return result;
    }

    public async Task<QHSEXyViolationClauseModel> GetByIdAsync(IdInputModel model)
    {
        var clause = await _context.Set<QHSESysXyViolationClause>().AsNoTracking().Where(u => u.GeneralSpecialtyId == model.Id).Join(_context.Set<QHSESysKeyWorkSpecialty>(),
            clause => clause.SpecialtyCode,
            specialty => specialty.SpecialtyCode,
            (clause, specialty) => new { Clause = clause, Specialty = specialty })
            .Select(join => new QHSESysXyViolationClause
            {
                GeneralSpecialtyId = join.Clause.GeneralSpecialtyId,
                SpecialtyCode = join.Clause.SpecialtyCode,
                ViolationTypeCode = join.Clause.ViolationTypeCode,
                Description = "(" + join.Specialty.Specialty + ")" + join.Clause.No + join.Clause.Description,
                Note = join.Clause.Note,
                No = join.Clause.No
            }
            ).FirstOrDefaultAsync();
        var models = MapperHelper.Map<QHSEXyViolationClauseModel>(clause);

        return models;
    }

}
